Imports DAL
Imports MyClasses
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DStock
    '@20071115 : ADD ONE MORE PARAMETER
    Public Shared Function GetStockDetails(ByVal stock As CStockQuery, ByVal _strStockType As String, ByVal ItemLike As String) As DataTable
        Dim strsql As String = ""
        Dim strcond As String = ""
        Try
            If stock.Company.Length > 0 Then strcond = " IT.mfctrID IN (" & stock.Company.ToString & ")"

            If stock.Location.Length > 0 Then
                If strcond.Length = 0 Then
                    strcond = " IT.LocationID IN (" & stock.Location.ToString & ")"
                Else
                    strcond = strcond & " AND IT.LocationID IN (" & stock.Location.ToString & ")"
                End If
            End If

            If stock.Vendor.Length > 0 Then
                If strcond.Length = 0 Then
                    strcond = " ST.VendorID IN (" & stock.Vendor.ToString & ")"
                Else
                    strcond = strcond & " AND ST.VendorID IN (" & stock.Vendor.ToString & ")"
                End If
            End If


            'NEAR TO EXPIRY
            If stock.Status = CStockQuery.StockStatus.NearExpiry Then
                If strcond.Length = 0 Then
                    strcond = " DateDiff (D,getdate(),ST.ExpDate) between 0 and " & ConfigurationManager.AppSettings("NearToExpiry")
                Else
                    strcond = strcond & " AND DateDiff (D,getdate(),ST.ExpDate) between 0 and " & ConfigurationManager.AppSettings("NearToExpiry")
                End If
            End If
            'EXPIRED
            If stock.Status = CStockQuery.StockStatus.Expired Then
                strsql = " SELECT tBreakage.Stockid, IT.SeqItemID, IT.ItemName, ST.CLRcode, " & _
                         " CAST(month(BreakageDate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp, " & _
                         " mLocation.LocationCode, tBreakage.Qty as Quantity, ST.PurchasePrice / ST.ConversionFactor as PurchasePrice, " & _
                         " tBreakage.Qty * (ST.PurchasePrice / ST.ConversionFactor) as Amount ,IT.ItemID" & _
                         " FROM ((TSTOCK ST INNER JOIN mPItem IT ON ST.ItemID = IT.ItemID) " & _
                         " INNER JOIN mLocation ON ST.LocationID = mLocation.LocationID) " & _
                         " INNER JOIN tBreakage ON ST.StockID = tBreakage.StockID " & _
                         " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & " AND tBreakage.Qty > 0 AND tBreakage.Status <> 'PR' AND tBreakage.Reason = 'EXP' "

                strsql = strsql & " UNION SELECT ST.Stockid, IT.SeqItemID, IT.ItemName, ST.CLRcode, " & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp, " & _
                        " mLocation.LocationCode, ST.Quantity as Quantity, ST.PurchasePrice / ST.ConversionFactor as PurchasePrice, " & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount ,IT.ItemID" & _
                        " FROM ((TSTOCK ST INNER JOIN mPItem IT ON ST.ItemID = IT.ItemID) " & _
                        " INNER JOIN mLocation ON ST.LocationID = mLocation.LocationID) " & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & " AND ST.Quantity > 0 "
                If strcond.Length = 0 Then
                    strcond = " DateDiff (D,getdate(),ST.ExpDate) < 0 "
                Else
                    strcond = strcond & " AND DateDiff (D,getdate(),ST.ExpDate) < 0 "
                End If
            Else
                If stock.AvailableOnly Then
                    If strcond.Length = 0 Then
                        strcond = " ST.Quantity >0 "
                    Else
                        strcond = strcond & " AND ST.Quantity >0 "
                    End If
                End If
                If ItemLike <> "" Then
                    If _strStockType = "SMR" Then
                        strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp," & _
                        " LC.LocationCode,ST.Quantity,ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount" & _
                        " FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID" & _
                        " INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID" & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & " AND ItemName LIKE '" & ItemLike & "%'"
                    ElseIf _strStockType = "PRA" Then

                        'Dim strRate As String = String.Empty
                        'Select Case ConfigurationManager.AppSettings("SaleRate")
                        '    Case "MRP"
                        '        strRate = "MRP"
                        '    Case "SALE1"
                        '        strRate = "SalePrice1"
                        '    Case "SALE2"
                        '        strRate = "SalePrice2"
                        'End Select
                        'strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        '" CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp," & _
                        '" LC.LocationCode,ST.Quantity,ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        '" ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount," & _
                        '" ((ST." & strRate & " - ST.PurchasePrice) * 100) / case ST.PurchasePrice WHEN 0 THEN 1 ELSE ST.PurchasePrice END as 'Current Margin',ST.SalePrice1 as 'Current Sale Price'," & _
                        '" '' as 'New Margin','' as 'New Sale Price',ST.ITEMID" & _
                        '" FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID" & _
                        '" INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID" & _
                        '" WHERE ItemName LIKE '" & ItemLike & "%'"
                        strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp," & _
                        " LC.LocationCode,ST.Quantity,ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount," & _
                        " ((ST.SalePrice1 - ST.PurchasePrice) * 100) / case ST.PurchasePrice WHEN 0 THEN 1 ELSE ST.PurchasePrice END as 'Current Margin',ST.SalePrice1 as 'Current Sale Price'," & _
                        " '' as 'New Margin','' as 'New Sale Price',ST.ITEMID" & _
                        " FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID" & _
                        " INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID" & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & " AND ItemName LIKE '" & ItemLike & "%'"
                    ElseIf _strStockType = "STKA" Then
                        strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp,LC.LocationCode," & _
                        " ST.Quantity as 'Available Stock',ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount," & _
                        " '' as 'New Stock',ST.ITEMID FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID" & _
                        " INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID" & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & " AND ItemName LIKE '" & ItemLike & "%'"
                    End If
                Else 'ALL ITEMS
                    If _strStockType = "SMR" Then   'SUMMARY
                        strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp,LC.LocationCode," & _
                        " ST.Quantity,ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount " & _
                        " FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID " & _
                        " INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID " & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & ""
                    ElseIf _strStockType = "PRA" Then 'PRICE ADJUSTMENT
                        ''Dim strRate As String = String.Empty
                        ''Select Case ConfigurationManager.AppSettings("SaleRate")
                        ''    Case "MRP"
                        ''        strRate = "MRP"
                        ''    Case "SALE1"
                        ''        strRate = "SalePrice1"
                        ''    Case "SALE2"
                        ''        strRate = "SalePrice2"
                        ''End Select
                        ''strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        ''" CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp,LC.LocationCode," & _
                        ''" ST.Quantity,ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        ''" ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount," & _
                        ''" ((ST." & strRate & "  - ST.PurchasePrice) * 100) / case ST.PurchasePrice WHEN 0 THEN 1 ELSE ST.PurchasePrice END as 'Current Margin',ST.SalePrice1 as 'Current Sale Price'," & _
                        ''" '' as 'New Margin','' as 'New Sale Price',ST.ITEMID" & _
                        ''" FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID" & _
                        ''" INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID"
                        strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp,LC.LocationCode," & _
                        " ST.Quantity,ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount," & _
                        " CASE ST.PurchasePrice WHEN 0 THEN 0 ELSE((ST.SalePrice1  - ST.PurchasePrice) * 100) / ST.PurchasePrice END AS 'Current Margin'," & _
                        " (ST.SalePrice1/ ST.ConversionFactor) AS 'Current Sale Price'," & _
                        " '' as 'New Margin','' as 'New Sale Price',ST.ITEMID " & _
                        " FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID " & _
                        " INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID " & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & ""
                    ElseIf _strStockType = "STKA" Then 'STOCK ADJUSTMENT
                        strsql = " SELECT ST.Stockid,IT.SeqItemID,IT.ItemName,ST.CLRcode," & _
                        " CAST(month(expdate)as VARCHAR) + '/' + RIGHT(CAST(year(expdate)as VARCHAR),2) as Exp,LC.LocationCode," & _
                        " ST.Quantity as 'Available Stock',ST.PurchasePrice / ST.ConversionFactor as PurchasePrice," & _
                        " ST.Quantity * (ST.PurchasePrice / ST.ConversionFactor) as Amount," & _
                        " '' as 'New Stock',ST.ITEMID " & _
                        " FROM TSTOCK ST INNER JOIN MPITEM IT ON ST.ITEMID=IT.ITEMID " & _
                        " INNER JOIN MLOCATION LC ON LC.LOCATIONID=IT.LOCATIONID " & _
                        " WHERE ST.FinyrID = " & ConfigurationManager.AppSettings("FinyrID") & ""
                    End If
                End If
            End If
            If strcond.Length <> 0 Then
                strsql = strsql & " AND " & strcond
            End If
            strsql = strsql & " ORDER BY IT.ItemID "
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetStockDetails")
            Return Nothing
        End Try
    End Function

    '@20071116 : Add new code
    Public Shared Function UpdateStock(ByVal Quantity As Integer, ByVal StockID As Integer, ByVal CLRcode As String, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Try
            strSql = "UPDATE tStock SET Quantity= " & Quantity & ", LU = '" & CCommon.GetUserID & "', " & _
                     "LD = " & Date.Today & " " & _
                     "WHERE stockid = " & StockID & " AND CLRcode = '" & CLRcode & "' "
            Return dbConnection.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateStock")
            Return 0
        End Try
    End Function

    '@20071116: Add new code
    Public Shared Function UpdateStockSummary(ByVal ItemID As Integer, ByVal Quantity As Integer, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Try
            If Quantity > 0 Then
                strSql = "CurrentStock=CurrentStock + " & Quantity
            Else
                Quantity = 0 - Quantity
                strSql = "CurrentStock=CurrentStock - " & Quantity
            End If
            strSql = "UPDATE tStockSummary SET " & strSql & ", LU = '" & CCommon.GetUserID & "', " & _
                     "LD = " & Date.Today & " " & _
                     "WHERE ItemID = " & ItemID & "  "
            Return dbConnection.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateStockSummary")
            Return Nothing
        End Try
    End Function

    '@20071116: Add new code
    Public Shared Function SaveStockAdjDtl(ByVal ItemID As Integer, ByVal CLRcode As String, ByVal StockID As Integer, ByVal OQty As Integer, ByVal NQty As Integer, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Dim TransID As Integer
        Dim param(7) As SqlParameter

        Try
            strSql = "INSERT INTO tStockAdjustment(StockTransID, ItemID, CLRcode, " & _
                     "StockID, OldQuantity, NewQuantity, LU, LD) " & _
                     "VALUES(@TransID, @ItemID, @CLRcode, @StockID, @OQty, @NQty, @LU, @LD )"

            TransID = DGeneral.GetSeqNum("DStock", dbConnection)

            param(0) = New SqlParameter("@TransID", TransID)
            param(1) = New SqlParameter("@ItemID", ItemID)
            param(2) = New SqlParameter("@CLRcode", CLRcode)
            param(3) = New SqlParameter("@StockID", StockID)
            param(4) = New SqlParameter("@OQty", OQty)
            param(5) = New SqlParameter("@NQty", NQty)
            param(6) = New SqlParameter("@LU", CCommon.GetUserID)
            param(7) = New SqlParameter("@LD", Date.Today)

            dbConnection.ExecuteNonQuery(strSql, param)
            Return 1
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "SaveStockAdjDtl")
            Return Nothing
        End Try
    End Function

    '@20071117
    Public Shared Function SavePrice(ByVal ItemID As Integer, ByVal CLRcode As String, _
           ByVal StockID As Integer, ByVal OMargin As Double, ByVal OPrice As Double, _
           ByVal NMargin As Double, ByVal NPrice As Double, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String = ""
        Dim TransID As Integer
        Dim param(9) As SqlParameter

        Try
            strSql = "INSERT INTO tstockPriceAdjustment(StockPriceTransID, ItemID, CLRcode, " & _
                     "StockID, OldMargin, OldSalePrice, NewMargin, NewSalePrice, LU, LD) " & _
                     "VALUES(@TransID, @ItemID, @CLRcode, @StockID, @OMrgn, @OPrice, @NMrgn, @NPrice, @LU, @LD )"

            TransID = DGeneral.GetSeqNum("DPrice", dbConnection)

            param(0) = New SqlParameter("@TransID", TransID)
            param(1) = New SqlParameter("@ItemID", ItemID)
            param(2) = New SqlParameter("@CLRcode", CLRcode)
            param(3) = New SqlParameter("@StockID", StockID)
            param(4) = New SqlParameter("@OMrgn", OMargin)
            param(5) = New SqlParameter("@OPrice", OPrice)
            param(6) = New SqlParameter("@NMrgn", NMargin)
            param(7) = New SqlParameter("@NPrice", NPrice)
            param(8) = New SqlParameter("@LU", CCommon.GetUserID)
            param(9) = New SqlParameter("@LD", Date.Today)

            dbConnection.ExecuteNonQuery(strSql, param)
            Return 1
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "SavePrice")
            Return Nothing
        End Try
    End Function

    '@20071117 : 
    Public Shared Function UpdateStockPrice(ByVal NPrice As Double, ByVal StockID As Integer, ByVal CLRcode As String, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Try
            strSql = "UPDATE TStock SET SalePrice1 = " & NPrice & ", LU = '" & CCommon.GetUserID & "', " & _
                     "LD = " & Date.Today & " " & _
                     "WHERE stockid = " & StockID & " AND CLRcode = '" & CLRcode & "' "
            Return dbConnection.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateStockPrice")
            Return 0
        End Try
    End Function

    '@20071117 : 
    Public Shared Function UpdateStockMargin(ByVal ItemID As Integer, ByVal NMargin As Double, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Try
            strSql = "UPDATE MpItem SET MarginComm = " & NMargin & ", LU = '" & CCommon.GetUserID & "', " & _
                     "LD = " & Date.Today & " " & _
                     "WHERE ItemID=" & ItemID & " "
            Return dbConnection.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateStockMargin")
            Return Nothing
        End Try
    End Function

    '@20071117
    '<< START
    Public Shared Function GetOldSalePrice(ByVal ItemId As Integer) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT SalePrice1 FROM tstock WHERE ItemID=" & ItemId & ""
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetOldSalePrice", strSql)
            Return Nothing
        End Try
    End Function
    '@20071116: Add new code
    Public Shared Function SaveStockAdj(ByVal ItemID As Integer, ByVal CLRcode As String, _
           ByVal StockID As Integer, ByVal OQty As Integer, ByVal NQty As Integer, ByVal qtycount As Integer) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0
        Dim intPurReturnID As Integer
        Try
            dbConnection.BeginTransaction()

            intPurReturnID = SaveStockAdjDtl(ItemID, CLRcode, StockID, OQty, NQty, dbConnection)
            If intPurReturnID = 0 Then Exit Function

            intResult = UpdateStock(NQty, StockID, CLRcode, dbConnection)
            If intResult = 0 Then Exit Function

            intResult = UpdateStockSummary(ItemID, qtycount, dbConnection)
            If intResult = 0 Then Exit Function

            dbConnection.CommitTransaction()
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DStock", "StockEnquiry", "SaveStockAdj")
            intResult = 0
        Finally
            If intResult = 0 Then
                dbConnection.RollBackTransaction()
                SaveStockAdj = intResult
            Else : SaveStockAdj = intPurReturnID
            End If
            dbConnection.closeSqlcon()
        End Try
    End Function

    '@20071117: Add new code
    Public Shared Function SavePriceAdj(ByVal ItemID As Integer, ByVal CLRcode As String, _
    ByVal StockID As Integer, ByVal OMargin As Double, ByVal OPrice As Double, _
    ByVal NMargin As Double, ByVal NPrice As Double) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0
        Dim intPurReturnID As Integer
        Try
            dbConnection.BeginTransaction()

            intPurReturnID = SavePrice(ItemID, CLRcode, StockID, OMargin, OPrice, NMargin, NPrice, dbConnection)
            If intPurReturnID = 0 Then Exit Function

            intResult = UpdateStockPrice(NPrice, StockID, CLRcode, dbConnection)
            If intResult = 0 Then Exit Function

            'intResult = UpdateStockMargin(ItemID, StockID, NMargin, dbConnection)
            'If intResult = 0 Then Exit Function

            dbConnection.CommitTransaction()
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DStock", "StockEnquiry", "SavePriceAdj")
            intResult = 0
        Finally
            If intResult = 0 Then
                dbConnection.RollBackTransaction()
                SavePriceAdj = intResult
            Else : SavePriceAdj = intPurReturnID
            End If
            dbConnection.closeSqlcon()
        End Try
    End Function

    '***********************************************************
    'Modified By:- (20071123) 
    'Getting Non Moving Item
    '====================
    Public Shared Function GetNonMoveItem(ByVal intDays As Integer) As DataTable
        Dim strSql As String = ""
        Try
            'strSql = "SELECT S.STOCKID,S.ITEMID,S.VENDORID,S.CLRcode, " & _
            '         "CAST(MONTH(S.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(S.EXPDATE)AS VARCHAR),2) AS EXPDATE, " & _
            '         "MP.ITEMNAME,S.QUANTITY,S.BILLRATE " & _
            '         "FROM TSTOCK S,MPITEM MP WHERE S.ITEMID = MP.ITEMID AND S.ITEMID NOT IN " & _
            '         "(SELECT ITEMID FROM TSALE TS INNER JOIN TSALEDET TD ON TS.SALEID=TD.SALEID " & _
            '         "WHERE DATEDIFF (D,TS.SALEDATE,GETDATE()) BETWEEN 1 AND " & intDays & ") AND S.QUANTITY > 0 " & _
            '         "AND DATEDIFF (D,S.LD,GETDATE()) > " & intDays & " ORDER BY MP.ITEMNAME "

            '***************************************************************
            'Modified By:-(2008/01/15)
            'After Discusssion with  and ajay.
            'Query Taken from Non-Moving Item Record.
            '========================================
            strSql = "SELECT S.STOCKID,S.ITEMID,S.VENDORID,S.CLRcode," & _
                     "CAST(MONTH(S.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(S.EXPDATE)AS VARCHAR),2) AS EXPDATE, " & _
                     "MP.ITEMNAME, S.QUANTITY,(S.PurchasePrice/S.ConversionFactor) As BILLRATE,S.PURCHASEPRICE " & _
                     "FROM TSTOCK S,MPITEM MP " & _
                     "WHERE S.ITEMID = MP.ITEMID AND S.QUANTITY > 0  AND S.ITEMID NOT IN " & _
                     "(SELECT ITEMID FROM TSALE TS INNER JOIN TSALEDET TD ON TS.SALEID=TD.SALEID " & _
                     "WHERE  DATEDIFF (D ,TS.SALEDATE,GETDATE()) BETWEEN 0 AND " & intDays & " UNION SELECT ITEMID FROM WSALEDET) " & _
                     "ORDER BY MP.ITEMNAME"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetNonMoveItem", strSql)
            Return Nothing
        End Try
    End Function
    '***********************************************************
    '_27/11/2007
    'Getting ExpiredItem 
    '====================
    Public Shared Function GetStockExpiredItem() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode,TS.EXPDATE,TS.QUANTITY,MP.ITEMNAME " & _
                     " FROM TSTOCK TS, MPITEM MP WHERE TS.ITEMID = MP.ITEMID AND  DATEDIFF(D,GETDATE(),TS.EXPDATE) < 0  AND TS.QUANTITY > 0"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetStockExpiredItem", strSql)
            Return Nothing
        End Try
    End Function
    '***********************************************************
    '_28/11/2007
    'Getting ExpiredItem 
    '====================
    Public Shared Function InsertExpiredItems(ByVal objItems() As CExpItem) As Integer
        Dim strSql As String = ""
        Dim BreakageID As Integer
        Dim intResult As Integer = 0
        Dim dbConnection As New dbAccess
        Dim param(7) As SqlParameter
        Try
            For Each Item As CExpItem In objItems
                If Not IsNothing(Item) Then
                    strSql = "INSERT INTO tBreakage(BreakageID,StockID,BreakageDate,Qty,Reason,LU,LD,Status) " & _
                                         "VALUES(@BreakageID,@StockID,@BreakageDate,@Qty,@Reason,@LU, @LD,@Status )"

                    BreakageID = DGeneral.GetSeqNum("BRKG", dbConnection)

                    param(0) = New SqlParameter("@BreakageID", BreakageID)
                    param(1) = New SqlParameter("@StockID", Item.StockID)
                    param(2) = New SqlParameter("@BreakageDate", Item.ExpiryDate)
                    param(3) = New SqlParameter("@Qty", Item.Quantity)
                    param(4) = New SqlParameter("@Reason", "EXP")
                    param(5) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(6) = New SqlParameter("@LD", Date.Today)
                    param(7) = New SqlParameter("@Status", "A")

                    intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
                    'Call UpdateTstockSummary(objItems)
                    Call UpdateStock(Item.ItemID, Item.Quantity, Item.CLRcode, "M", Item.StockID, dbConnection)
                    Call SaveStockRegister(Item.ItemID, Item.Quantity, Date.Today, "EX", dbConnection)
                End If

            Next
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "InsertExpiredItems", strSql)
            Return Nothing
        End Try
    End Function
    '***********************************************************
    '_28/11/2007
    'Getting ExpiredItem 
    '====================
    Public Shared Function UpdateTstockSummary(ByVal objItems() As CExpItem) As Boolean
        Dim dbConnection As New dbAccess
        Dim strSql As String = ""
        Dim intResult As Integer = 0
        Dim param(3) As SqlParameter

        Try
            For Each Item As CExpItem In objItems
                If Not IsNothing(Item) Then
                    strSql = "Update tStockSummary set CurrentStock =CurrentStock -@CurrentStock , LU=@LU, LD=@LD " & _
                             " where ItemID = @ItemID"
                    param(0) = New SqlParameter("@CurrentStock", IIf(IsNothing(Item.Quantity), 0, Item.Quantity))
                    param(1) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(2) = New SqlParameter("@LD", Date.Today)
                    param(3) = New SqlParameter("@ItemID", Item.ItemID)
                    intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)

                    'Call UpdateStock(Item.ItemID, Item.Quantity, Item.CLRcode, "M", Item.StockID, dbConnection)
                End If
            Next
            Return True
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateTstockSummary", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function DeleteStockItems()
        Dim strSql As String = ""
        Try
            strSql = "Delete from "
            Return DbHelper.ExecuteNonQuery(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "DeleteStockItems", strSql)
            Return Nothing
        End Try
    End Function

    '***********************************************************
    'Modified By:- (20071123) 
    'Getting ExpiredItem 
    '====================
    Public Shared Function GetExpiredItem(ByVal strType As String) As DataTable
        Dim strSql As String = ""
        Try
            If strType = "Expired" Then
                'strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode, " & _
                '         "CAST(MONTH(TS.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TS.EXPDATE)AS VARCHAR),2) AS EXPDATE, " & _
                '         "MP.ITEMNAME,TB.QTY AS QUANTITY,TS.BILLRATE,TB.REASON " & _
                '         "FROM TSTOCK TS,TBREAKAGE TB,MPITEM MP WHERE TS.STOCKID=TB.STOCKID " & _
                '         "AND TS.ITEMID = MP.ITEMID AND TB.REASON = 'EXP' AND TS.Quantity > 0 ORDER BY TS.VENDORID"
                'strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode," & _
                '         "CAST(MONTH(TS.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TS.EXPDATE)AS VARCHAR),2) AS EXPDATE," & _
                '         "TS.QUANTITY,MP.ITEMNAME,(TS.PurchasePrice/TS.ConversionFactor) As BILLRATE " & _
                '         "FROM TSTOCK TS, MPITEM MP WHERE TS.ITEMID = MP.ITEMID " & _
                '         "AND TS.QUANTITY > 0 AND EXPDATE < GetDate() ORDER BY MP.ITEMNAME"
                'strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode," & _
                '         "CAST(MONTH(TS.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TS.EXPDATE)AS VARCHAR),2) AS EXPDATE," & _
                '         "TS.QUANTITY,MP.ITEMNAME,(TS.PurchasePrice/TS.ConversionFactor) As BILLRATE " & _
                '         "FROM TSTOCK TS, MPITEM MP WHERE TS.ITEMID = MP.ITEMID " & _
                '         "AND TS.QUANTITY > 0 AND DATEDIFF(D,GETDATE(),TS.EXPDATE) < 0" & _
                '         " UNION SELECT TB.STOCKID,MP.ITEMID,TS.VENDORID,TS.CLRcode, " & _
                '         "CAST(MONTH(TB.BreakageDate)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TB.BreakageDate)AS VARCHAR),2) AS EXPDATE, " & _
                '         "TB.QTY AS QUANTITY,MP.ITEMNAME,(TS.PurchasePrice/TS.ConversionFactor) As BILLRATE " & _
                '         "FROM TSTOCK TS,TBREAKAGE TB,MPITEM MP WHERE TS.STOCKID=TB.STOCKID " & _
                '         "AND TS.ITEMID = MP.ITEMID AND TB.REASON = 'EXP' AND TB.Status<>'PR' AND " & _
                '         "TB.QTY > 0 ORDER BY MP.ITEMNAME"

                strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode," & _
                         "CAST(MONTH(TS.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TS.EXPDATE)AS VARCHAR),2) AS EXPDATE," & _
                         "TS.QUANTITY,MP.ITEMNAME,(TS.PurchasePrice/TS.ConversionFactor) As BILLRATE " & _
                         "FROM TSTOCK TS, MPITEM MP WHERE TS.ITEMID = MP.ITEMID " & _
                         "AND TS.QUANTITY > 0 AND DATEDIFF(D,GETDATE(),TS.EXPDATE) < 0" & _
                         "ORDER BY MP.ITEMNAME"



            ElseIf strType = "Breakage" Then
                'strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode, " & _
                '         "CAST(MONTH(TS.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TS.EXPDATE)AS VARCHAR),2) AS EXPDATE, " & _
                '         "MP.ITEMNAME,TB.QTY AS QUANTITY,TS.BILLRATE,TB.REASON " & _
                '         "FROM TSTOCK TS,TBREAKAGE TB,MPITEM MP WHERE TS.STOCKID=TB.STOCKID " & _
                '         "AND TS.ITEMID = MP.ITEMID AND TB.REASON <> 'EXP' AND TB.Status='A' " & _
                '         "AND TS.Quantity > 0 ORDER BY TS.VENDORID"
                'Modified By:-(2008/01/08)
                strSql = "SELECT TS.STOCKID,TS.ITEMID,TS.VENDORID,TS.CLRcode, " & _
                         "CAST(MONTH(TS.EXPDATE)AS VARCHAR) + '/' + RIGHT(CAST(YEAR(TS.EXPDATE)AS VARCHAR),2) AS EXPDATE, " & _
                         "MP.ITEMNAME,TB.QTY AS QUANTITY,(TS.PurchasePrice/TS.ConversionFactor) As BILLRATE,TB.REASON " & _
                         "FROM TSTOCK TS,TBREAKAGE TB,MPITEM MP WHERE TS.STOCKID=TB.STOCKID " & _
                         "AND TS.ITEMID = MP.ITEMID AND TB.Status='A' AND TB.QTY > 0 " & _
                         "ORDER BY MP.ITEMNAME"
            End If
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetExpiredItem", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function SaveItems(ByVal objItems() As CExpItem) As Integer
        Dim strSql As String = ""
        Dim BreakageID As Integer
        Dim intResult As Integer = 0
        Dim dbConnection As New dbAccess
        Dim param(7) As SqlParameter
        Try
            For Each Item As CExpItem In objItems
                If Not IsNothing(Item) Then
                    strSql = "INSERT INTO tBreakage(BreakageID,StockID,BreakageDate,Qty,Reason,LU,LD,Status) " & _
                                         "VALUES(@BreakageID,@StockID,@BreakageDate,@Qty,@Reason,@LU, @LD,@Status )"

                    BreakageID = DGeneral.GetSeqNum("BRKG", dbConnection)

                    param(0) = New SqlParameter("@BreakageID", BreakageID)
                    param(1) = New SqlParameter("@StockID", Item.StockID)
                    param(2) = New SqlParameter("@BreakageDate", Item.BreakageDate)
                    param(3) = New SqlParameter("@Qty", Item.Quantity)
                    param(4) = New SqlParameter("@Reason", IIf(IsNothing(Item.Reason), String.Empty, Item.Reason))
                    param(5) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(6) = New SqlParameter("@LD", Date.Today)
                    param(7) = New SqlParameter("@Status", Item.Status)

                    intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next

            'If intResult > 0 And strFlag = "NIandEX" Then
            '    Call UpdateExpReturnStockDetail(objItems, dbConnection)
            'End If

            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "SaveItems", strSql)
            Return Nothing
        End Try
    End Function

    '***********************************************************************
    'Written By:-(20071214)
    'Save Purchase Return Data From Breakage Item Form
    '=================================================
    Public Shared Function UpdateExpReturnStockDetail(ByRef Items() As CExpItem, ByVal strItem As String) As Integer
        Dim i As Integer
        Dim dbConnection As New dbAccess
        Try
            For Each Item As CExpItem In Items
                If Not IsNothing(Item) Then
                    i = i + UpdateStock(Item.ItemID, Item.Quantity, Item.CLRcode, "M", Item.StockID, dbConnection)    '  StockDetail(Item, VendorID, dbConnection)
                    'Modified By:-(20071205)
                    Call SaveStockRegister(Item.ItemID, Item.Quantity, Item.BreakageDate, strItem, dbConnection)
                    'Modified By:-(20071205)
                    Call saveStockSummary(Item.ItemID, Item.Quantity, 0, 0, "M", dbConnection, True)
                End If
            Next
            'DGeneral.SetSeqno("SREG", SRegID - 1)
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateExpReturnStockDetail")
            Return 0
        End Try
    End Function


    Public Shared Function SaveStockRegister(ByVal ItemId As Integer, ByVal Quantity As Integer, ByVal StockDate As Date, ByVal RegType As String, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Dim StrUpdateField As String = ""

        Try
lblStart:
            Select Case RegType
                Case "P"            'Purchase Invoice
                    StrUpdateField = "PurStock=Purstock +"
                Case "S"            'Sale 
                    StrUpdateField = "SaleStock=SaleStock +"
                Case "PR"           'Purchase Return
                    StrUpdateField = "PurRStock=PurRStock +"
                Case "SR"           'Sale Return
                    StrUpdateField = "SaleRStock=SaleRStock +"
                Case "BR"           'Breakage Items
                    StrUpdateField = "BrekageStock=BrekageStock +"
                Case "EX"
                    StrUpdateField = "ExpiredStock=ExpiredStock +"
                    '@ on 20070103:Added for Opening Stock
                Case "OS"
                    StrUpdateField = "OpenStock=OpenStock +"
            End Select

            strSql = "UPDATE tStockRegister SET " & StrUpdateField & Quantity & "  WHERE ItemID=" & ItemId & " AND StockDate='" & Format(StockDate, "yyyy/MM/dd") & "' "

            If dbConnection.ExecuteNonQuery(strSql) <= 0 Then
                'If RegType <> "OS" Then Quantity = 0
                Dim parm(12) As SqlParameter
                strSql = " INSERT INTO tstockregister(StockRegID, ItemID,StockDate, OpenStock, SaleStock, PurStock ," & _
                         " SaleRStock,PurRStock,BrekageStock, ExpiredStock, LU,LD, FinYrID )" & _
                         " VALUES(@StockRegID,@ItemID,@StockDate,@OpenStock,@SaleStock,@PurStock,@SaleRStock," & _
                         " @PurRStock,@BrekageStock,@ExpiredStock,@LU,@LD,@FinYrID)"

                parm(0) = New SqlParameter("@StockRegID", DGeneral.GetSeqNum("SREG", dbConnection, True))
                parm(1) = New SqlParameter("@ItemID", ItemId)
                parm(2) = New SqlParameter("@StockDate", StockDate.Date)
                parm(3) = New SqlParameter("@OpenStock", 0)
                parm(4) = New SqlParameter("@SaleStock", 0)
                parm(5) = New SqlParameter("@PurStock", 0)
                parm(6) = New SqlParameter("@SaleRStock", 0)
                parm(7) = New SqlParameter("@PurRStock", 0)
                parm(8) = New SqlParameter("@BrekageStock", 0)
                parm(9) = New SqlParameter("@ExpiredStock", 0)
                parm(10) = New SqlParameter("@LU", CCommon.GetUserID)
                parm(11) = New SqlParameter("@LD", Date.Now)
                parm(12) = New SqlParameter("@FinYrID", CCommon.GetFinYrID)
                If dbConnection.ExecuteNonQuery(strSql, parm) > 0 Then GoTo lblStart
            Else : Return 1
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "SaveStockRegister")
            Return 0
        End Try
    End Function

    Public Shared Function saveStockSummary(ByVal ItemID As Integer, ByVal Quantity As Integer, ByVal SchemeQty As Integer, ByVal InvoiceID As Integer, ByVal chrOperator As Char, ByRef dbConnection As dbAccess, Optional ByVal UpdateStockOnly As Boolean = False) As Integer
        'chrOperator  -> P- Plus
        '                M- Minus 
        Dim strSql As String = ""
        Dim param(2) As SqlParameter
        Dim strUpdateField As String = ""

        Try
            If chrOperator = "P" Then
                strSql = " UPDATE tstocksummary SET CurrentStock=CurrentStock + @CurrentStock,SchemStock=SchemStock + @SchemStock"
                param(0) = New SqlParameter("@CurrentStock", Quantity)
                param(1) = New SqlParameter("@SchemStock", SchemeQty)

            ElseIf chrOperator = "M" Then
                strSql = " UPDATE tstocksummary SET CurrentStock=CurrentStock - @CurrentStock,SchemStock=SchemStock - @SchemStock"
                param(0) = New SqlParameter("@CurrentStock", Quantity)
                param(1) = New SqlParameter("@SchemStock", SchemeQty)
            End If

            If UpdateStockOnly Then
                param(2) = New SqlParameter("@ItemID", ItemID)
            Else
                ReDim Preserve param(3)
                If chrOperator = "P" Then
                    strUpdateField = " ,LastPurchaseInvoice=@LastPurchaseInvoice "
                    param(2) = New SqlParameter("@LastPurchaseInvoice", InvoiceID)
                ElseIf chrOperator = "M" Then
                    strUpdateField = " ,LastSaleBill=@LastSaleBill "
                    param(2) = New SqlParameter("@LastSaleBill", InvoiceID)
                End If
                strSql = strSql & strUpdateField
                param(3) = New SqlParameter("@ItemID", ItemID)
            End If
            strSql = strSql & " WHERE ItemID=@ItemID"
            Return dbConnection.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "saveStockSummary")
            Return 0
        End Try
    End Function


    Public Shared Function UpdateStock(ByVal ItemID As Integer, ByVal Quantity As Integer, ByVal CLRcode As String, ByVal PlusOrMinus As Char, ByVal StockID As Integer, ByRef dbConnection As dbAccess) As Integer
        'Dim strCurrentStock As Integer
        Dim param(3) As SqlParameter
        Dim strSql As String = ""
        Try
            '************************
            If PlusOrMinus = "P" Then
                strSql = "UPDATE tStock SET Quantity=Quantity + @Quantity  WHERE ItemID=@ItemID AND CLRcode=@CLRcode AND VendorID=@VendorID"
            ElseIf PlusOrMinus = "M" Then
                strSql = "UPDATE tStock SET Quantity=Quantity - @Quantity WHERE ItemID=@ItemID AND CLRcode=@CLRcode  AND StockID=@StockID AND Quantity >= @Quantity"
            End If
            param(0) = New SqlParameter("@Quantity", Quantity)
            param(1) = New SqlParameter("@ItemID", ItemID)
            param(2) = New SqlParameter("@CLRcode", CLRcode)
            param(3) = New SqlParameter("@StockID", StockID)
            Return dbConnection.ExecuteNonQuery(strSql, param)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateStock")
            Return 0
        End Try
    End Function


    Public Shared Function GetStockBatchs(ByVal ItemID As Integer) As DataTable
        Dim strSql As String = ""

        Try

            Select Case ConfigurationManager.AppSettings("SaleRate")
                'Modified By:-(2008/01/21)=Round the year value to Two place
                Case "MRP"
                    strSql = "SELECT CLRcode as Batch,CAST(month(ExpDate)as VARCHAR) + '/' + Right(CAST(year(ExpDate)as VARCHAR),2) as Expiry, " & _
                     " SUM(Quantity) as Stock,MRP as SalePrice,ConversionFactor,PurchasePrice,ISNULL(mScheme.SchemeID,0) AS SchemeID,SchemeItemQty,MRP " & _
                     " FROM tStock LEFT OUTER JOIN mScheme ON tStock.SchemeID=mScheme.SchemeID AND CONVERT(VARCHAR, mScheme.ValidTo,1) >= CONVERT(VARCHAR, GETDATE(),1) " & _
                     " WHERE Quantity > 0 AND DateDiff (D,getdate(),tStock.ExpDate) >=0 AND tStock.ItemID = " & ItemID & _
                     " GROUP BY tStock.ItemID ,CLRcode ,ExpDate,MRP,ConversionFactor,PurchasePrice,mScheme.SchemeID,SchemeItemQty"

                Case "SALE1"
                    strSql = "SELECT CLRcode as Batch,CAST(month(ExpDate)as VARCHAR) + '/' + Right(CAST(year(ExpDate)as VARCHAR),2) as Expiry, " & _
                     " SUM(Quantity) as Stock,SalePrice1 as SalePrice,ConversionFactor,PurchasePrice,ISNULL(mScheme.SchemeID,0) AS SchemeID,SchemeItemQty,MRP  " & _
                     " FROM tStock LEFT OUTER JOIN mScheme ON tStock.SchemeID=mScheme.SchemeID AND CONVERT(VARCHAR, mScheme.ValidTo,1) >= CONVERT(VARCHAR, GETDATE(),1) " & _
                     " WHERE(Quantity > 0 AND DateDiff (D,getdate(),tStock.ExpDate) >=0 And tStock.ItemID = " & ItemID & ")" & _
                     " GROUP BY tStock.ItemID ,CLRcode ,ExpDate,SalePrice1,ConversionFactor,PurchasePrice,mScheme.SchemeID,SchemeItemQty"

                Case "SALE2"
                    strSql = "SELECT CLRcode as Batch,CAST(month(ExpDate)as VARCHAR) + '/' + Right(CAST(year(ExpDate)as VARCHAR),2) as Expiry, " & _
                     " SUM(Quantity) as Stock,SalePrice2 as SalePrice,ConversionFactor,PurchasePrice,ISNULL(mScheme.SchemeID,0) AS SchemeID,SchemeItemQty,MRP  " & _
                     " FROM tStock LEFT OUTER JOIN mScheme ON tStock.SchemeID=mScheme.SchemeID AND CONVERT(VARCHAR, mScheme.ValidTo,1) >= CONVERT(VARCHAR, GETDATE(),1) " & _
                     " WHERE(Quantity > 0 AND DateDiff (D,getdate(),tStock.ExpDate) >=0 And tStock.ItemID = " & ItemID & ")" & _
                     " GROUP BY tStock.ItemID ,CLRcode ,ExpDate,SalePrice2,ConversionFactor,PurchasePrice,mScheme.SchemeID,SchemeItemQty"
            End Select


            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetStockBatchs", strSql)
            Return Nothing
        End Try
    End Function


    '*********************************************************
    'Modifying the Status of the Breakage Item
    'Written By:-(20071228)
    '===========================
    Public Shared Function UpdateBrkItemsStatus(ByVal objItems() As CExpItem) As Integer
        Dim strSql As String = ""
        Dim intResult As Integer = 0
        Dim dbConnection As New dbAccess
        Dim param(3) As SqlParameter
        Try
            For Each Item As CExpItem In objItems
                If Not IsNothing(Item) Then
                    strSql = "UPDATE tBreakage Set Status=@Status,LU=@LU,LD=@LD where StockID=@StockID"

                    param(0) = New SqlParameter("@Status", Item.Status)
                    param(1) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(2) = New SqlParameter("@LD", Date.Today)
                    param(3) = New SqlParameter("@StockID", Item.StockID)

                    intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateBreakageItems", strSql)
            Return Nothing
        End Try
    End Function

    '*********************************************************
    'Modifying the Quantity of the Breakage Item
    'Written By:-(2008/01/11)
    '===========================
    Public Shared Function UpdateBrkItemsQty(ByVal objItems() As CExpItem) As Integer
        Dim strSql As String = ""
        Dim intResult As Integer = 0
        Dim dbConnection As New dbAccess
        Dim param(3) As SqlParameter
        Try
            For Each Item As CExpItem In objItems
                If Not IsNothing(Item) Then
                    strSql = "UPDATE tBreakage Set Qty=Qty-@Quantity,LU=@LU,LD=@LD where StockID=@StockID"

                    param(0) = New SqlParameter("@Quantity", Item.Quantity)
                    param(1) = New SqlParameter("@LU", CCommon.GetUserID)
                    param(2) = New SqlParameter("@LD", Date.Today)
                    param(3) = New SqlParameter("@StockID", Item.StockID)

                    intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "UpdateBreakageItems", strSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetRoles(ByVal p_role As String) As DataTable
        Dim strSql As String = ""
        Dim param(0) As SqlParameter
        Try
            strSql = "SELECT RoleID as RoleId " & _
                     "FROM mpuser Where userid = @rolename"
            param(0) = New SqlParameter("@rolename", p_role)

            Return DbHelper.ExecuteDataTable(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRoles", "GetRoles", strSql)
            Return Nothing
        End Try
    End Function
    ' @06/03/2008 
    Public Shared Function GetMRP(ByVal ItemID As Integer, ByVal Batch As String) As Integer
        Dim strSql As String = ""
        Try
            strSql = "SELECT MRP from tStock where ItemID =" & ItemID & " AND CLRcode = '" & Batch & "'"
            Return DbHelper.ExecuteScalar(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DStock", "GetMRP", strSql)
            Return Nothing
        End Try
    End Function
End Class

